﻿Imports DTO
Imports System.Data.OleDb
Public Class CHocSinhDAO

    Public Function ThemHocSinh(ByVal _HSdto As CHocSinhDTO) As Integer
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strinsert As String = "insert into HOCSINH(HoTen,NgaySinh,Email,GioiTinh,DiaChi) values('" + _HSdto.HoTen + "','" + _HSdto.NgaySinh + "','" + _HSdto.Email + "','" + _HSdto.GioiTinh + "','" + _HSdto.DiaChi + "')"
        Dim cmd As OleDbCommand = New OleDbCommand(_strinsert, cnn)
        Dim _kt As Integer = cmd.ExecuteNonQuery()
        cnn.Close()
        Return _kt
    End Function

    Public Function LayDanhSachHSCrt() As List(Of CHocSinhCRT1)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strselect As String = "select hs.MaHocSinh,hs.HoTen,hs.NgaySinh,hs.Email,hs.GioiTinh,hs.DiaChi,ctl.MaLop,l.TenLop from HOCSINH hs,CHITIETLOP ctl,LOP l where hs.MaHocSinh=ctl.MaHocSinh and ctl.MaLop=l.MaLop"
        Dim cmd As New OleDbCommand(_strselect, cnn)
        Dim _reader As OleDbDataReader = cmd.ExecuteReader()
        Dim _danhsachhs As New List(Of CHocSinhCRT1)
        While _reader.Read()
            Dim _HScrt As New CHocSinhCRT1()
            _HScrt.MaHS = _reader.GetInt32(0)
            _HScrt.HoTen = _reader.GetString(1)
            _HScrt.NgaySinh = _reader.GetDateTime(2)
            _HScrt.Email = _reader.GetString(3)
            _HScrt.GioiTinh = _reader.GetString(4)
            _HScrt.DiaChi = _reader.GetString(5)
            _HScrt.MaLop = _reader.GetInt32(6)
            _HScrt.TenLop = _reader.GetString(7)
            _danhsachhs.Add(_HScrt)
        End While
        cnn.Close()
        Return _danhsachhs
    End Function

    Public Function LayDanhSachHS() As List(Of CHocSinhDTO)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strselect As String = "select MaHocSinh,HoTen,NgaySinh,Email,GioiTinh,DiaChi from HOCSINH where MaHocSinh not in (select MaHocSinh from CHITIETLOP)"
        Dim cmd As New OleDbCommand(_strselect, cnn)
        Dim _reader As OleDbDataReader = cmd.ExecuteReader()
        Dim _danhsachhs As New List(Of CHocSinhDTO)
        While _reader.Read()
            Dim _HSdto As New CHocSinhDTO()
            _HSdto.MaHS = _reader.GetInt32(0)
            _HSdto.HoTen = _reader.GetString(1)
            _HSdto.NgaySinh = _reader.GetDateTime(2)
            _HSdto.Email = _reader.GetString(3)
            _HSdto.GioiTinh = _reader.GetString(4)
            _HSdto.DiaChi = _reader.GetString(5)
            _danhsachhs.Add(_HSdto)
        End While
        cnn.Close()
        Return _danhsachhs
    End Function

    Public Function LayTatCaHS() As List(Of CHocSinhDTO)
        Dim _cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        _cnn.Open()
        Dim _strsql = "select MaHocSinh,HoTen,NgaySinh,Email,GioiTinh,DiaChi from HOCSINH"
        Dim _cmd As New OleDbCommand(_strsql, _cnn)
        Dim _reader As OleDbDataReader = _cmd.ExecuteReader()
        Dim _ds As New List(Of CHocSinhDTO)
        While _reader.Read()
            Dim _HSdto As New CHocSinhDTO()
            _HSdto.MaHS = _reader.GetInt32(0)
            _HSdto.HoTen = _reader.GetString(1)
            _HSdto.NgaySinh = _reader.GetDateTime(2)
            _HSdto.Email = _reader.GetString(3)
            _HSdto.GioiTinh = _reader.GetString(4)
            _HSdto.DiaChi = _reader.GetString(5)
            _ds.Add(_HSdto)
        End While
        _cnn.Close()
        Return _ds
    End Function

    Public Function LayDSHocSinhAdapter() As DataTable
        Dim _cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        _cnn.Open()
        Dim _strsql = "select MaHocSinh,HoTen,NgaySinh,Email,GioiTinh,DiaChi from HOCSINH"
        Dim _adapter As New OleDbDataAdapter(_strsql, _cnn)
        Dim _dtt As New DataTable()
        _adapter.Fill(_dtt)
        _cnn.Close()
        Return _dtt
    End Function

    Private Function BuildQuery(ByVal _HScrt As CHocSinhCRT, ByVal cn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand()
        Dim strDKTenHocSinh = " 1=1 "
        Dim strDKNgaySinh = " 1=1 "
        Dim strDKDiaChi = " 1=1 "
        Dim strDKLopHoc = " 1=1 "

        If (_HScrt.HoTen <> "") Then
            strDKTenHocSinh = " hs.HoTen like ? "
            cmd.Parameters.Add("@HoTen", OleDbType.WChar)
            cmd.Parameters("@HoTen").Value = "%" + _HScrt.HoTen + "%"
        End If
        If (_HScrt.CheckNgaySinh) Then
            strDKNgaySinh = " hs.NgaySinh between ? and ? "
            cmd.Parameters.Add("@NgaySinhTu", OleDbType.Date)
            cmd.Parameters.Add("@NgaySinhDen", OleDbType.Date)
            cmd.Parameters("@NgaySinhTu").Value = _HScrt.NgaySinhtu
            cmd.Parameters("@NgaySinhDen").Value = _HScrt.NgaySinhden
        End If
        If (_HScrt.CheckDiaChi And _HScrt.DiaChi <> "") Then
            strDKDiaChi = " hs.DiaChi like ? "
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%" + _HScrt.DiaChi + "%"
        End If

        If (_HScrt.CheckLopHoc) Then
            strDKLopHoc = "l.MaLop = ? "
            cmd.Parameters.Add("@l.MaLop", OleDbType.Integer)
            cmd.Parameters("@L.MaLop").Value = _HScrt.MaLop
        End If

        Dim strDKWhere As String = " Where hs.MaHocSinh = ctl.MaHocSinh and ctl.MaLop=l.MaLop and "
        strDKWhere += strDKTenHocSinh
        strDKWhere += " and " + strDKNgaySinh
        strDKWhere += " and " + strDKDiaChi
        strDKWhere += " and " + strDKLopHoc
        Dim strSQL As String = "Select hs.MaHocSinh,hs.Hoten,l.MaLop,l.TenLop from HOCSINH hs,CHITIETLOP ctl,LOP l "
        strSQL += strDKWhere

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd
    End Function

    Public Function TraCuuHocSinh(ByVal _HScrt As CHocSinhCRT) As List(Of CHocSinhCRT)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _cmd As OleDbCommand = BuildQuery(_HScrt, cnn)
        Dim _reader As OleDbDataReader = _cmd.ExecuteReader()
        Dim _danhsachhs As New List(Of CHocSinhCRT)
        Dim _danhsachdiem As New List(Of CChiTietDiemMonHocCRT)
        _danhsachdiem = CChiTietDiemMonHocDAO.TinhDiemTB()
        While _reader.Read()
            Dim _HScrt1 As New CHocSinhCRT()
            _HScrt1.MaHocSinh = _reader.GetInt32(0)
            _HScrt1.HoTen = _reader.GetString(1)
            _HScrt1.MaLop = _reader.GetInt32(2)
            _HScrt1.TenLop = _reader.GetString(3)
            _danhsachhs.Add(_HScrt1)
        End While
        cnn.Close()
        Dim i, j As Integer
        For i = 0 To _danhsachhs.Count - 1
            For j = 0 To _danhsachdiem.Count - 1
                If _danhsachhs(i).MaHocSinh = _danhsachdiem(j).MaHocSinh Then
                    _danhsachhs(i).TBHKI = _danhsachdiem(j).DiemTB
                    _danhsachhs(i).TBHKII = _danhsachdiem(j).DiemTB
                    Exit For
                End If
            Next
        Next
        Return _danhsachhs
    End Function

    Public Function CapNhatHS(ByVal _HScrt1 As CHocSinhCRT1) As Integer
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strcapnhaths = "update HOCSINH set Hoten='" + _HScrt1.HoTen + "',NgaySinh='" + _HScrt1.NgaySinh + "',Email='" + _HScrt1.Email + "',GioiTinh='" + _HScrt1.GioiTinh + "',DiaChi='" + _HScrt1.DiaChi + "' where MaHocSinh=" + _HScrt1.MaHS.ToString() + ""
        Dim _cmd As New OleDbCommand(_strcapnhaths, cnn)
        Dim _kt As Integer = _cmd.ExecuteNonQuery()
        cnn.Close()
        Return _kt
    End Function

    Public Function CapNhatHSAdapter(ByVal _dtt As DataTable) As Integer
        Dim _cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        _cnn.Open()
        Dim _strsql = "select MaHocSinh,HoTen,NgaySinh,Email,GioiTinh,DiaChi from HOCSINH"
        Dim _adapter As New OleDbDataAdapter(_strsql, _cnn)
        Dim _builder As New OleDbCommandBuilder(_adapter)
        Dim _kt = _adapter.Update(_dtt)
        _cnn.Close()
        Return _kt
    End Function

End Class
